package com.example.common.utils;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.format.*;
import jxl.write.*;

import java.io.File;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


/**
 * 导出Excel工具类
 * @author lindr
 */
public class ExportExcelUtil {
	private Connection con = null;

	private WritableSheet sheet = null;

	private WritableWorkbook book;
	
	private String excelName = "";

	/**
	 * 取得一个连接对象
	 */
	public void getCon(Connection con) throws Exception {
		this.con = con;
	}
	
	/**
	 * 创建Excel文件
	 * */
	public String creatExcelFile(String path,String excelName) throws Exception{
		this.excelName = excelName;
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		String newFileName = df.format(new Date()) + "_"+ RandomValidateCodeUtil.nextInt(10000)  +"/";
		path = path + "/exportexcel/" + newFileName;
		File dirFile = new File(path);
		if (!dirFile.exists()) {
			dirFile.mkdirs();
		}
		path = path + excelName+".xls";
		//创建excel
		this.book= Workbook.createWorkbook(new File(path)); 
		//生成名为“第一页”的工作表，参数0表示这是第一页 
		this.sheet=book.createSheet(excelName,0); 
		return path;
	}

	/**
	 * 载入Excel指定文件
	 */
	public void openExcel(String path,int num) throws Exception {// Excel获得文件

		Workbook wb = Workbook.getWorkbook(new File(path)); // 打开一个文件的副本，并且指定数据写回到原文件
		book = Workbook.createWorkbook(new File(path), wb); // 添加一个工作表
		sheet = book.getSheet(num);
	}

	/**
	 * 载入数据库的数据添加到Excel中
	 * 返回导入Excel的记录数
	 */
	public boolean importData(List<Map<String, String>> dataList) throws Exception{
		if(null == dataList || dataList.size()==0){
			return false;
		}
		
		Map firstMap = dataList.get(0);
		int colCnt = firstMap.size();
		int [] columnWith = new int [colCnt];//列宽数组
		/*sheet.mergeCells(0, 0,colCnt-1, 0);
		Label firstLabel = new Label(0,0,excelName);
		//设置第1行字体大小和字体位置
	    WritableFont font1 = new  WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
        WritableCellFormat cellFormat1=new WritableCellFormat(font1);
        cellFormat1.setAlignment(Alignment.CENTRE);
        cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
        firstLabel.setCellFormat(cellFormat1);
		sheet.addCell(firstLabel);
		sheet.setRowView(0, 450);*/
		
		//设置第2行字体大小和字体位置
	    /*WritableFont font2 = new  WritableFont(WritableFont.ARIAL,8,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
        WritableCellFormat cellFormat2=new WritableCellFormat(font2);
        cellFormat2.setAlignment(Alignment.CENTRE);
        cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
		for(int k=0;k<colCnt;k++){
			String title = (String)firstMap.get(String.valueOf(k));
			Label labelTitle = new Label(k,1,title);
			byte[] bstrLength = title.getBytes();   //中文字符算两个字节
			int colWith = bstrLength.length+2;
			sheet.setColumnView(k, colWith); //设置列宽为标题长度
			columnWith[k] = colWith;
	        labelTitle.setCellFormat(cellFormat2);
			sheet.addCell(labelTitle);
		}*/
		//sheet.setRowView(1, 350);
		//设置正文字体大小和字体位置
	    //WritableFont font3 = new  WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
        WritableCellFormat cellFormat3=new WritableCellFormat();
        cellFormat3.setAlignment(Alignment.CENTRE);
        cellFormat3.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat3.setBorder(Border.ALL, BorderLineStyle.THIN);
		int i = 1;
		for (Iterator iterator = dataList.iterator(); iterator.hasNext();) {
			Map<String, String> dataMap = (Map<String, String>) iterator.next();
			for (int j = 0; j < colCnt; j++) {
				String colValue = (String)dataMap.get(String.valueOf(j+1));
				byte[] bstrLength = colValue.getBytes();   //中文字符算两个字节
				int colWith = bstrLength.length+2;
				if(colWith>columnWith[j]){//取最大长度设为列宽
					sheet.setColumnView(j, colWith);
					columnWith[j]=colWith;
				}
				sheet.setRowView(i, 350);
				Label label = new Label(j, i, colValue);
				label.setCellFormat(cellFormat3);
				sheet.addCell(label);
			}
			i++;
		}
		book.write();
		book.close();
		
		return true;
	}
	
	
	/**
	 * 载入数据库的数据添加到Excel中
	 * 返回导入Excel的记录数
	 */
	public boolean importDatas(List<Map<String, String>> dataList) throws Exception{
		if(null == dataList || dataList.size()==0){
			return false;
		}
		
		Map firstMap = dataList.get(0);
		int colCnt = firstMap.size();
		//设置第2行字体大小和字体位置
	    WritableFont font2 = new  WritableFont(WritableFont.ARIAL,8,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
        WritableCellFormat cellFormat2=new WritableCellFormat(font2);
        cellFormat2.setAlignment(Alignment.CENTRE);
        cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
		int [] columnWith = new int [colCnt];//列宽数组
		for(int k=0;k<colCnt;k++){
			String title = (String)firstMap.get(String.valueOf(k));
			Label labelTitle = new Label(k,0,title);
			byte[] bstrLength = title.getBytes();   //中文字符算两个字节
			int colWith = bstrLength.length+2;
			sheet.setColumnView(k, colWith); //设置列宽为标题长度
			columnWith[k] = colWith;
	        labelTitle.setCellFormat(cellFormat2);
			sheet.addCell(labelTitle);
		}
		sheet.setRowView(1, 350);
		//设置正文字体大小和字体位置
	    //WritableFont font3 = new  WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
        WritableCellFormat cellFormat3=new WritableCellFormat();
        cellFormat3.setAlignment(Alignment.CENTRE);
        cellFormat3.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat3.setBorder(Border.ALL, BorderLineStyle.THIN);
		int i = 1;
		for (Iterator iterator = dataList.iterator(); iterator.hasNext();) {
			Map<String, String> dataMap = (Map<String, String>) iterator.next();
			if(i==1){
				i++;
				continue;
			}
			for (int j = 0; j < colCnt; j++) {
				String colValue = (String)dataMap.get(String.valueOf(j));
				byte[] bstrLength = colValue.getBytes();   //中文字符算两个字节
				int colWith = bstrLength.length+2;
				if(colWith>columnWith[j]){//取最大长度设为列宽
					sheet.setColumnView(j, colWith);
					columnWith[j]=colWith;
				}
				sheet.setRowView(i, 350);
				Label label = new Label(j, i-1, colValue);
				label.setCellFormat(cellFormat3);
				sheet.addCell(label);
			}
			i++;
		}
		book.write();
		book.close();
		
		return true;
	}
}
